<?php
class Attendance extends AppModel 
{
	var $name = 'Attendance';
	var $belongsTo = 'User';
	
	function getData($listName)
	{
		//the date which will be shown
		$date = (isset($_GET['datum'])) ? $_GET['datum'] : date('Y-m-d');
		
		//calculate the last day of the week
		$endoftheweek = strtotime($date);
		while(date('N', $endoftheweek) != 6)
		{
			$endoftheweek = strtotime("+1 day", $endoftheweek);
		}
	
		$week[0]['end'] = date('Y-m-d', $endoftheweek);
		$week[0]['start'] = date('Y-m-d', strtotime('-6 day', $endoftheweek));
		$week[1]['end'] = date('Y-m-d', strtotime('-1 week', $endoftheweek));
		$week[1]['start'] = date('Y-m-d', strtotime('-1 week -6 day', $endoftheweek));
		$week[2]['end'] = date('Y-m-d', strtotime('-2 week', $endoftheweek));
		$week[2]['start'] = date('Y-m-d', strtotime('-2 week -6 day', $endoftheweek));
		
		$this->virtualFields['weekone'] = sprintf(
			'SUM(CASE WHEN Attendance.date BETWEEN \'%s\' AND \'%s\' THEN Attendance.type ELSE 0 END)', $week[0]['start'], $week[0]['end']
		);
		$this->virtualFields['weektwo'] = sprintf(
			'SUM(CASE WHEN Attendance.date BETWEEN \'%s\' AND \'%s\' THEN Attendance.type ELSE 0 END)', $week[1]['start'], $week[1]['end']
		);
		$this->virtualFields['weekthree'] = sprintf(
			'SUM(CASE WHEN Attendance.date BETWEEN \'%s\' AND \'%s\' THEN Attendance.type ELSE 0 END)', $week[2]['start'], $week[2]['end']
		);
		$this->virtualFields['weektotal'] = sprintf(
			'SUM(CASE WHEN Attendance.date BETWEEN \'%s\' AND \'%s\' THEN Attendance.type ELSE 0 END)', $week[2]['start'], $week[0]['end']
		);
		$this->virtualFields['total'] = 'SUM(type)';
		
		$datestart = date("Y-m-d", strtotime("-1 day", strtotime($week[2]['start'])));
		
		$min_date = ("2010-07-31" < $datestart) ? "2010-07-31" : $datestart;
		
		$data['endoftheweek'] = $endoftheweek;
		$data['attendances'] =  $this->find('all', array(
			'fields' => array('User.username', 'Attendance.weekone', 'Attendance.weektwo', 'Attendance.weekthree', 'Attendance.weektotal', 'Attendance.total'),
			'group' => 'Attendance.user_id',
			'order' => array(
				'Attendance.weektotal DESC', 
				'Attendance.weekone DESC', 
				'Attendance.total DESC'
			),
			'conditions' => array(
				"date < '{$week[0]['end']}'",
				"date >= '{$min_date}'"
			)
		));
		
		return $data;
	}
		
	function getTopAttendances($amount)
	{
        $this->recursive = 0;
		//find the next saturday
		$endoftheweek = time();
		while(date('N', $endoftheweek) != 6)
		{
			$endoftheweek = strtotime("+1 day", $endoftheweek);
		}
		
		
		$end = date('Y-m-d', $endoftheweek);
		$start = date('Y-m-d', strtotime('-3 week', $endoftheweek));

		$this->virtualFields['weektotal'] = sprintf(
			'SUM(CASE WHEN Attendance.date BETWEEN \'%s\' AND \'%s\' THEN Attendance.type ELSE 0 END)', $start, $end
		);
		$this->virtualFields['total'] = 'SUM(type)';

		return $this->find('all', array(
			'fields' => array('User.username', 'Attendance.weektotal', 'Attendance.total'),
			'group' => 'Attendance.user_id',
			'limit' => $amount,
			'order' => array('weektotal DESC', 'total DESC'),
			'conditions' => "Attendance.date >= '2010-07-31'"
		));
	}
}
?>